package com.nccsjz.back.customer.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import utils.DBUtils;

import com.nccsjz.pojo.Customer;
import com.nccsjz.pojo.Merchant;
import com.nccsjz.pojo.News;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.base.BaseDAO;
import com.nccsjz.utils.Pager;

/**
 * 商户的CRUD操作
 * */
public class CustomerDAO extends BaseDAO {

	private boolean flag;

	public CustomerDAO(Connection con) {
		super(con);
	}

	// 添加卡户
	public boolean addCus(Customer cus) throws SQLException {
		try {
			String sql = "insert into customer(customerno,customerpwd,customerphone,customeraddr,customeremail,customerregdate,customerstate,customercodeno,tailcode) values(?,?,?,?,?,?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			//pstmt.setLong(1, cus.getMid());
			pstmt.setString(1, cus.getMname());
			pstmt.setString(2, cus.getMpwd());
			pstmt.setString(3, cus.getMphone());
			pstmt.setString(4, cus.getMaddr());
			pstmt.setString(5, cus.getMemail());
			//pstmt.setDate(6, cus.getMdate());
			if (null != cus.getMdate()) {
				pstmt.setDate(6, cus.getMdate());
			} else {
				pstmt.setDate(6, DateUtil.convertDate(new java.util.Date()));
			}
			pstmt.setLong(7, 0);
			pstmt.setString(8, cus.getCodeno());
			pstmt.setString(9, cus.getTailcode());			
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 根据主键删除卡户
	public boolean deleteCus(long id) throws SQLException {
		try {
			// conn=super.getConn();
			String sql = "delete from customer where customerid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num > 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 分页处理
	public Pager getCusList(int pageSize, int pageNo) throws SQLException {
		List<Customer> list = new ArrayList<Customer>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from customer";
			//String sql2 = "select * from(select u.*,rownum rn from(select * from customer order by customerid ) u where rownum<=?)where rn>?";

			String sql2 = "select * from (select customer.*, row_number() over(order by customerid desc) rn " 
					+ "from customer) u where u.rn > ? and u.rn <= ?";
			
			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Customer cus = new Customer();
				cus.setMid(rs.getLong("customerid"));
				cus.setMname(rs.getString("customerno"));
				cus.setMaddr(rs.getString("customeraddr"));
				cus.setMdate(rs.getDate("customerregdate"));
				cus.setMemail(rs.getString("customeremail"));
				cus.setMphone(rs.getString("customerphone"));
				cus.setMstat(rs.getLong("customerstate"));
				cus.setCodeno(rs.getString("customercodeno"));
				cus.setTailcode(rs.getString("tailcode"));
				list.add(cus);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	/**
	 * 得到前n条商户。
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Merchant> getTopN(int n) throws SQLException {
		List<Merchant> list = new ArrayList<Merchant>();
		int rownum = 0;
		try {
			if (n <= 0) {
				throw new Exception("取得的记录条数不能为0或负数");
			}
			String sql2 = "select * from(select u.*,rownum rn from(select * from merchant order by merchantid desc) u )where rn<=?";
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, n);

			rs = pstmt.executeQuery();
			while (rs.next() && rownum <= n) {
				Merchant merch = new Merchant();
				merch.setMid(rs.getLong("merchantid"));
				merch.setMname(rs.getString("merchantname"));
				merch.setMaddr(rs.getString("merchantaddr"));
				merch.setMphone(rs.getString("merchantphone"));
				merch.setMdate(rs.getDate("merchantregdate"));
				merch.setMdesc(rs.getString("merchantdecr"));
				merch.setMstat(rs.getLong("merchantstate"));
				merch.setMcode(rs.getString("merchantcode"));				
				list.add(merch);
				rownum++;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return list;
	}

	/**
	 * 根据 name,pwd取得卡户
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public Customer getCusByNameAndPwd(String name, String pwd)
			throws SQLException {
		Customer cus = new Customer();
		String sql = "select * from customer where customerno=? and customerpwd=?";
		this.pstmt = con.prepareStatement(sql);
		pstmt.setString(1, name);
		pstmt.setString(2, pwd);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			cus.setMid(rs.getLong("customerid"));
			cus.setMname(rs.getString("customerno"));
			cus.setMaddr(rs.getString("customeraddr"));
			cus.setMdate(rs.getDate("customerregdate"));
			cus.setMemail(rs.getString("customeremail"));
			cus.setMphone(rs.getString("customerphone"));
			cus.setCodeno(rs.getString("customercodeno"));
			cus.setTailcode(rs.getString("tailcode"));
		}
		return cus;
	}

	/**
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Customer getCus(long id) throws SQLException {
		Customer cus = new Customer();
		String sql = "select * from customer where customerid=?";
		this.pstmt = con.prepareStatement(sql);
		pstmt.setLong(1, id);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			cus.setMid(rs.getLong("customerid"));
			cus.setMname(rs.getString("customerno"));
			cus.setMaddr(rs.getString("customeraddr"));
			cus.setMdate(rs.getDate("customerregdate"));
			cus.setMemail(rs.getString("customeremail"));
			cus.setMphone(rs.getString("customerphone"));
			cus.setMpwd(rs.getString("customerpwd"));
			cus.setCodeno(rs.getString("customercodeno"));
			cus.setTailcode(rs.getString("tailcode"));
		}
		return cus;
	}

	public boolean update(Customer merch) throws SQLException {
		boolean f = false;
		String sql = "update customer set customerno=?,customeraddr=? ,customeremail=?,customerphone=?,customerpwd=?,customercodeno=?,tailcode = ?  where customerid=?";
		this.pstmt = con.prepareStatement(sql);
		pstmt.setString(1, merch.getMname());
		pstmt.setString(2, merch.getMaddr());
		pstmt.setString(3, merch.getMemail());
		pstmt.setString(4, merch.getMphone());
		pstmt.setString(5, merch.getMpwd());		
		pstmt.setString(6, merch.getCodeno());
		pstmt.setString(7, merch.getTailcode());
		pstmt.setLong(8, merch.getMid());
		int n = pstmt.executeUpdate();
		if (n > 0) {
			f = true;
		}
		return f;
	}

	public Pager getCusList(int pageSize, int pageNo, Customer merch)
			throws SQLException {
		List<Customer> list = new ArrayList<Customer>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from customer";			
			//String sql2 = "select * from(select u.*,rownum rn from(select * from customer where customercodeno=? order by customerid ) u where rownum<=?)where rn>?";
			String sql2 = "select * from (select customer.*, row_number() over(order by customerid desc) rn from customer"; 
			String id = String.valueOf(merch.getCodeno());
			String cid = null;
			boolean flag = StringUtils.isNotBlank(id);
			if(flag){
				cid = "%" + id + "%";
				sql1 += " where customercodeno like ? ";
				sql2 += " where customercodeno like ? ";
			}		
			sql2 += " ) u where u.rn > ? and u.rn <= ?";
			pstmt = con.prepareStatement(sql1);
			if(flag){
				pstmt.setString(1, cid);
			}			
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			if(flag){
				pstmt.setString(1, cid);
				pstmt.setInt(2, (pageNo - 1) * pageSize);
				pstmt.setInt(3, pageNo * pageSize);			
			}else{
				pstmt.setInt(1, (pageNo - 1) * pageSize);
				pstmt.setInt(2, pageNo * pageSize);
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Customer cus = new Customer();
				cus.setMid(rs.getLong("customerid"));
				cus.setMname(rs.getString("customerno"));
				cus.setMaddr(rs.getString("customeraddr"));
				cus.setMdate(rs.getDate("customerregdate"));
				cus.setMemail(rs.getString("customeremail"));
				cus.setMphone(rs.getString("customerphone"));
				cus.setMstat(rs.getLong("customerstate"));
				cus.setCodeno(rs.getString("customercodeno"));
				cus.setTailcode(rs.getString("tailcode"));
				list.add(cus);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}
	
	/**
	 * isCustExistsByName方法用于判断指定用户名的卡户是否存在
	 * 
	 * @param name
	 * @return
	 * @throws SQLException 
	 */
	public boolean isCustExistsByName(String name) throws SQLException{
		boolean flag = false;
		try{
			String sql = "select 1 from CUSTOMER where CUSTOMERNO = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}finally{
			DBUtils.CloseRs(rs);
			DBUtils.CloseStmt(pstmt);
		}
		return flag;		
	}
	
	/**
	 * initPwdById方法根据卡户编号初始化卡户密码
	 * 
	 * @param id
	 * @throws SQLException
	 */
	public void initPwdById(Long id) throws SQLException{
		try{
			String sql = "update CUSTOMER set CUSTOMERPWD = ? where CUSTOMERID = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "111111");
			pstmt.setLong(2, id);
			pstmt.executeUpdate();
		}finally{
			DBUtils.CloseStmt(pstmt);
		}
	}

}
